****************************************************
*******PREPARE WORDSCORE DATASET 1990-2017**********
****************************************************

// This data have been eleborated in R using the R.code "Code_SpeechesAndAids_textasdata1". 

///////////////////////
// FOR USA VS RUSSIA //
///////////////////////

clear all
cd "C:\Users\your_location"

* I save all the excel files with wordscore values in dta file (all string)

clear all
forvalues y = 1971(1)2018{
import excel "C:\Users\your_location\USAungd_data_`y'.xlsx", sheet("Sheet 1") firstrow
save UN_wordscoreUSA`y', replace
clear all
}

* I put all the years of word scores dta files togheder

clear all

use UN_wordscoreUSA1971
forvalues y = 1972(1)2018{
append using UN_wordscoreUSA`y'
}

destring wordscore , replace
 
// Some country did not speak every years, 
// I do the following to fill with missing values the years in which these countries did not speak 

encode Country , generate(Country1)
tsset Country1 Year
tsfill, full

decode Country1 , generate(Country2)
drop Country
drop Country1
rename Country2 Country

rename wordscore wordscoreUSA , replace

save UN_wordscore_allUSA, replace 

////////////////////////////////
// FOR USA VS RUSSIA PLACEBO1 //
////////////////////////////////

clear all
cd "C:\Users\your_location"

* I save all the excel files with wordscore values in dta file (all string)
clear all
forvalues y = 1971(1)2018{
import excel "C:\Users\your_location\USAungd_data_`y'P1.xlsx", sheet("Sheet 1") firstrow
save UN_wordscoreUSA`y'P1, replace
clear all
}

* I put all the years of word scores dta files togheder
clear all

use UN_wordscoreUSA1971P1
forvalues y = 1972(1)2018{
append using UN_wordscoreUSA`y'P1
}

destring wordscore , replace
 
// Some country did not speak every years, 
// I do the following to fill with missing values the years in which these countries did not speak 
encode Country , generate(Country1)
tsset Country1 Year
tsfill, full

decode Country1 , generate(Country2)
drop Country
drop Country1
rename Country2 Country

rename wordscore wordscoreUSAP1 , replace

// I already have the wordscore in relative terms

save UN_wordscore_allUSAP1, replace 

////////////////////////////////
// FOR USA VS RUSSIA PLACEBO2 //
////////////////////////////////

clear all
cd "C:\Users\your_location"

* I save all the excel files with wordscore values in dta file (all string)
clear all
forvalues y = 1971(1)2018{
import excel "C:\Users\your_location\USAungd_data_`y'P2.xlsx", sheet("Sheet 1") firstrow
save UN_wordscoreUSA`y'P2, replace
clear all
}

* I put all the years of word scores dta files togheder
clear all

use UN_wordscoreUSA1971P2
forvalues y = 1972(1)2018{
append using UN_wordscoreUSA`y'P2
}

destring wordscore , replace
 
// Some country did not speak every years, 
// I do the following to fill with missing values the years in which these countries did not speak 
encode Country , generate(Country1)
tsset Country1 Year
tsfill, full

decode Country1 , generate(Country2)
drop Country
drop Country1
rename Country2 Country

rename wordscore wordscoreUSAP2 , replace

// I already have the wordscore in relative terms

save UN_wordscore_allUSAP2, replace 

////////////////////////////////
// FOR USA VS RUSSIA PLACEBO3 //
////////////////////////////////

clear all
cd "C:\Users\your_location"

* I save all the excel files with wordscore values in dta file (all string)
clear all
forvalues y = 1971(1)2018{
import excel "C:\Users\your_location\USAungd_data_`y'P3.xlsx", sheet("Sheet 1") firstrow
save UN_wordscoreUSA`y'P3, replace
clear all
}

* I put all the years of word scores dta files togheder
clear all

use UN_wordscoreUSA1971P3
forvalues y = 1972(1)2018{
append using UN_wordscoreUSA`y'P3
}

destring wordscore , replace
 
// Some country did not speak every years, 
// I do the following to fill with missing values the years in which these countries did not speak 
encode Country , generate(Country1)
tsset Country1 Year
tsfill, full

decode Country1 , generate(Country2)
drop Country
drop Country1
rename Country2 Country

rename wordscore wordscoreUSAP3 , replace

// I already have the wordscore in relative terms

save UN_wordscore_allUSAP3, replace 

////////////////////////////////
// FOR USA VS RUSSIA PLACEBO4 //
////////////////////////////////

clear all
cd "C:\Users\your_location"

* I save all the excel files with wordscore values in dta file (all string)
clear all
forvalues y = 1971(1)2018{
import excel "C:\Users\your_location\USAungd_data_`y'P4.xlsx", sheet("Sheet 1") firstrow
save UN_wordscoreUSA`y'P4, replace
clear all
}

* I put all the years of word scores dta files togheder
clear all

use UN_wordscoreUSA1971P4
forvalues y = 1972(1)2018{
append using UN_wordscoreUSA`y'P4
}

destring wordscore , replace
 
// Some country did not speak every years, 
// I do the following to fill with missing values the years in which these countries did not speak 
encode Country , generate(Country1)
tsset Country1 Year
tsfill, full

decode Country1 , generate(Country2)
drop Country
drop Country1
rename Country2 Country

rename wordscore wordscoreUSAP4 , replace

// I already have the wordscore in relative terms

save UN_wordscore_allUSAP4, replace 

